﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;

namespace Common
{
    public class PDAL
    {

        #region Menu
        public class PMenu
        {
            public PMenu() { }

            public DataSet GetList(string where)
            {
                StringBuilder sql = new StringBuilder();
                sql.Append("select * from P_Menu");
                if (!string.IsNullOrEmpty(where))
                {
                    sql.Append(" where " + where);
                }
                return OledbHelper.Query(sql.ToString());
            }

            public OleDbDataReader GetDr(string where)
            {
                StringBuilder sql = new StringBuilder();
                sql.Append("select * from P_Menu");
                if (!string.IsNullOrEmpty(where))
                {
                    sql.Append(" where " + where);
                }
                return OledbHelper.ExecuteReader(sql.ToString());
            }

            public PModel.PMenu GetModel(string id)
            {
                StringBuilder sql = new StringBuilder();
                sql.Append("select * from P_Menu where Id=" + id);
                DataTable dt = OledbHelper.ExecuteQuery(sql.ToString());
                PModel.PMenu model = new PModel.PMenu();
                if (dt.Rows.Count > 0)
                {
                    model = ModelHandler<PModel.PMenu>.FillModel(dt.Rows[0]);
                }
                return model;
            }

            public bool Update(PModel.PMenu model)
            {
                StringBuilder sql = new StringBuilder();
                sql.Append("update P_Menu set MCn=@mCn,MEn=@mEn,MInfo=@mInfo where Id=@Id");
                OleDbParameter[] parameters = {
					new OleDbParameter("@mCn", OleDbType.VarChar,50),
					new OleDbParameter("@mEn", OleDbType.VarChar,50),
                    new OleDbParameter("@mInfo", OleDbType.VarChar),
                    new OleDbParameter("@Id", OleDbType.Integer)};
                parameters[0].Value = model.MCn;
                parameters[1].Value = model.MEn;
                parameters[2].Value = model.MInfo;
                parameters[3].Value = model.Id;
                return OledbHelper.ExecuteSql(sql.ToString(), parameters) > 0 ? true : false;
            }

            public bool Insert(PModel.PMenu model)
            {
                StringBuilder sql = new StringBuilder();
                sql.Append("insert into P_Menu (MCn,MEn,MInfo) values (@mCn,@mEn,@mInfo)");
                OleDbParameter[] parameters = {
					new OleDbParameter("@mCn", OleDbType.VarChar,50),
					new OleDbParameter("@mEn", OleDbType.VarChar,50),
                    new OleDbParameter("@mInfo", OleDbType.VarChar)};
                parameters[0].Value = model.MCn;
                parameters[1].Value = model.MEn;
                parameters[2].Value = model.MInfo;
                return OledbHelper.ExecuteSql(sql.ToString(), parameters) > 0 ? true : false;
            }
        }
        #endregion

        #region Config
        public class PConfig
        {
            public PConfig() { }

            public PModel.PConfig GetModel()
            {
                StringBuilder sql = new StringBuilder();
                sql.Append("select * from P_Config where Id=1");
                DataTable dt = OledbHelper.ExecuteQuery(sql.ToString());
                PModel.PConfig model = new PModel.PConfig();
                if (dt.Rows.Count > 0)
                {
                    model = ModelHandler<PModel.PConfig>.FillModel(dt.Rows[0]);
                }
                return model;
            }

            public bool SetConfig(PModel.PConfig model)
            {
                StringBuilder sql = new StringBuilder();
                sql.Append("update P_Config set ");
                sql.Append("P_WebPath=@P_WebPath,");
                sql.Append("P_Title=@P_Title,");
                sql.Append("P_Footer=@P_Footer,");
                sql.Append("P_Name=@P_Name,");
                sql.Append("P_Des=@P_Des,");
                sql.Append("P_Key=@P_Key,");
                sql.Append("P_Ipc=@P_Ipc,");
                sql.Append("P_Tel=@P_Tel,");
                sql.Append("P_Fax=@P_Fax,");
                sql.Append("P_Add=@P_Add,");
                sql.Append("P_Contact=@P_Contact,");
                sql.Append("P_Phone=@P_Phone,");
                sql.Append("P_WStatus=@P_WStatus,");
                sql.Append("P_WTrans=@P_WTrans,");
                sql.Append("P_MapY=@P_MapY,");
                sql.Append("P_MapX=@P_MapX");
                sql.Append(" where Id=@Id");
                OleDbParameter[] parameters = {
                    new OleDbParameter("@P_WebPath", OleDbType.VarChar,50),
                    new OleDbParameter("@P_Title", OleDbType.VarChar,255),
                    new OleDbParameter("@P_Footer", OleDbType.VarChar),
                    new OleDbParameter("@P_Name", OleDbType.VarChar,50),
                    new OleDbParameter("@P_Des", OleDbType.VarChar),
                    new OleDbParameter("@P_Key", OleDbType.VarChar),
                    new OleDbParameter("@P_Ipc", OleDbType.VarChar,50),
                    new OleDbParameter("@P_Tel", OleDbType.VarChar,50),
                    new OleDbParameter("@P_Fax", OleDbType.VarChar,50),
                    new OleDbParameter("@P_Add", OleDbType.VarChar,255),
                    new OleDbParameter("@P_Contact", OleDbType.VarChar,100),
                    new OleDbParameter("@P_Phone", OleDbType.VarChar,50),
                    new OleDbParameter("@P_WStatus", OleDbType.Integer),
                    new OleDbParameter("@P_WTrans", OleDbType.Integer),
                    new OleDbParameter("@P_MapY", OleDbType.VarChar,50),
                    new OleDbParameter("@P_MapX", OleDbType.VarChar,50),
                    new OleDbParameter("@Id", OleDbType.VarChar,50)};
                parameters[0].Value = model.P_WebPath;
                parameters[1].Value = model.P_Title;
                parameters[2].Value = model.P_Footer;
                parameters[3].Value = model.P_Name;
                parameters[4].Value = model.P_Des;
                parameters[5].Value = model.P_Key;
                parameters[6].Value = model.P_Ipc;
                parameters[7].Value = model.P_Tel;
                parameters[8].Value = model.P_Fax;
                parameters[9].Value = model.P_Add;
                parameters[10].Value = model.P_Contact;
                parameters[11].Value = model.P_Phone;
                parameters[12].Value = model.P_WStatus;
                parameters[13].Value = model.P_WTrans;
                parameters[14].Value = model.P_MapY;
                parameters[15].Value = model.P_MapX;
                parameters[16].Value = model.Id;
                return OledbHelper.ExecuteSql(sql.ToString(), parameters) > 0 ? true : false;
            }
        }
        #endregion

        #region Service
        public class PService
        {
            public PService() { }

            public DataSet GetList(string where)
            {
                StringBuilder sql = new StringBuilder();
                sql.Append("select * from P_Service");
                if (!string.IsNullOrEmpty(where))
                {
                    sql.Append(" where " + where);
                }
                sql.Append(" order by SXort asc");
                return OledbHelper.Query(sql.ToString());
            }

            public OleDbDataReader GetList()
            {
                StringBuilder sql = new StringBuilder();
                sql.Append("select * from P_Service order by SXort asc");
                return OledbHelper.ExecuteReader(sql.ToString());
            }

            public OleDbDataReader GetDr(string id)
            {
                StringBuilder sql = new StringBuilder();
                sql.Append("select * from P_Service where Id=" + id);
                return OledbHelper.ExecuteReader(sql.ToString());
            }

            /// <summary>
            /// 返回数据总数(分页用到)
            /// </summary>
            public int GetCount(string strWhere)
            {
                StringBuilder sql = new StringBuilder();
                sql.Append("select count(*) as H ");
                sql.Append(" from P_Service ");
                if (strWhere.Trim() != "")
                {
                    sql.Append(" where " + strWhere);
                }
                return Convert.ToInt32(OledbHelper.GetSingle(sql.ToString()));
            }

            /// <summary>
            /// 获得查询分页数据
            /// </summary>
            public DataSet GetPageList(int pageSize, int currentPage, string strWhere, string filedOrder)
            {
                int topNum = pageSize * currentPage;
                StringBuilder strSql = new StringBuilder();

                strSql.Append("select top " + pageSize + " * from P_Service");
                if (currentPage > 0)
                {
                    strSql.Append(" where Id not in( select top " + topNum + " Id from P_Service");
                    if (strWhere.Trim() != "")
                    {
                        strSql.Append(" where " + strWhere);
                    }
                    strSql.Append(" order by " + filedOrder + ")");
                }
                if (strWhere.Trim() != "")
                {
                    if (currentPage > 0)
                    {
                        strSql.Append(" and " + strWhere);
                    }
                    else
                    {
                        strSql.Append(" where " + strWhere);
                    }
                }
                strSql.Append(" order by " + filedOrder);

                return OledbHelper.Query(strSql.ToString());
            }
        }
        #endregion

        #region Ad
        public class PAd
        {
            public PAd() { }

            public bool Exists(string where)
            {
                StringBuilder sql = new StringBuilder();
                sql.Append("select * from P_Ad");
                if (!string.IsNullOrEmpty(where))
                {
                    sql.Append(" where " + where);
                }
                return OledbHelper.Exists(sql.ToString());
            }

            public DataSet GetList(string where)
            {
                StringBuilder sql = new StringBuilder();
                sql.Append("select * from P_Ad");
                if (!string.IsNullOrEmpty(where))
                {
                    sql.Append(" where " + where);
                }
                return OledbHelper.Query(sql.ToString());
            }
        }
        #endregion

        #region Show
        public class PShow
        {
            public PShow() { }

            public DataSet GetList()
            {
                StringBuilder sql = new StringBuilder();
                sql.Append("select * from P_Show");
                return OledbHelper.Query(sql.ToString());
            }

            /// <summary>
            /// 返回数据总数(分页用到)
            /// </summary>
            public int GetCount(string strWhere)
            {
                StringBuilder sql = new StringBuilder();
                sql.Append("select count(*) as H ");
                sql.Append(" from P_Show ");
                if (strWhere.Trim() != "")
                {
                    sql.Append(" where " + strWhere);
                }
                return Convert.ToInt32(OledbHelper.GetSingle(sql.ToString()));
            }

            /// <summary>
            /// 获得查询分页数据
            /// </summary>
            public DataSet GetPageList(int pageSize, int currentPage, string strWhere, string filedOrder)
            {
                int topNum = pageSize * currentPage;
                StringBuilder strSql = new StringBuilder();

                strSql.Append("select top " + pageSize + " * from P_Show");
                if (currentPage > 0)
                {
                    strSql.Append(" where Id not in( select top " + topNum + " Id from P_Show");
                    if (strWhere.Trim() != "")
                    {
                        strSql.Append(" where " + strWhere);
                    }
                    strSql.Append(" order by " + filedOrder + ")");
                }
                if (strWhere.Trim() != "")
                {
                    if (currentPage > 0)
                    {
                        strSql.Append(" and " + strWhere);
                    }
                    else
                    {
                        strSql.Append(" where " + strWhere);
                    }
                }
                strSql.Append(" order by " + filedOrder);

                return OledbHelper.Query(strSql.ToString());
            }

            public PModel.PShow GetModel(string id)
            {
                StringBuilder sql = new StringBuilder();
                sql.Append("select * from P_Show where Id=" + id);
                DataTable dt = OledbHelper.ExecuteQuery(sql.ToString());
                PModel.PShow model = new PModel.PShow();
                if (dt.Rows.Count > 0)
                {
                    model = ModelHandler<PModel.PShow>.FillModel(dt.Rows[0]);
                }
                return model;
            }

            public bool Update(PModel.PShow model)
            {
                StringBuilder sql = new StringBuilder();
                sql.Append("update P_Show set SName=@sName,SPic=@sPic where Id=@Id");
                OleDbParameter[] parameters = {
					new OleDbParameter("@sName", OleDbType.VarChar,50),
					new OleDbParameter("@SPic", OleDbType.VarChar,80),
                    new OleDbParameter("@Id", OleDbType.Integer)};
                parameters[0].Value = model.SName;
                parameters[1].Value = model.SPic;
                parameters[2].Value = model.Id;
                return OledbHelper.ExecuteSql(sql.ToString(), parameters) > 0 ? true : false;
            }

            public bool Insert(PModel.PShow model)
            {
                StringBuilder sql = new StringBuilder();
                sql.Append("insert into P_Show (SName,SPic) values (@sName,@sPic)");
                OleDbParameter[] parameters = {
					new OleDbParameter("@sName", OleDbType.VarChar,50),
					new OleDbParameter("@SPic", OleDbType.VarChar,80)};
                parameters[0].Value = model.SName;
                parameters[1].Value = model.SPic;
                return OledbHelper.ExecuteSql(sql.ToString(), parameters) > 0 ? true : false;
            }
        }
        #endregion

        #region Link
        public class PLink
        {
            public PLink() { }

            public DataSet GetList()
            {
                StringBuilder sql = new StringBuilder();
                sql.Append("select * from P_Link");
                return OledbHelper.Query(sql.ToString());
            }
        }
        #endregion

        #region News
        public class PNews
        {
            public PNews() { }

            public DataSet GetList(string where)
            {
                StringBuilder sql = new StringBuilder();
                sql.Append("select * from P_News");
                if (!string.IsNullOrEmpty(where))
                {
                    sql.Append(" where " + where);
                }
                sql.Append(" order by NTime desc");
                return OledbHelper.Query(sql.ToString());
            }

            public OleDbDataReader GetDr(string id)
            {
                StringBuilder sql = new StringBuilder();
                sql.Append("select * from P_News where Id=" + id);
                return OledbHelper.ExecuteReader(sql.ToString());
            }

            public OleDbDataReader GetPageList(int size, int page)
            {
                StringBuilder sql = new StringBuilder();
                sql.Append("select top " + size + " * from P_News");
                if (page > 0)
                {
                    sql.Append(" where Id not in (select top " + (size * page) + " Id from P_News order by Id desc)");
                }
                sql.Append(" order by Id desc");
                return OledbHelper.ExecuteReader(sql.ToString());
            }

            public int GetCount()
            {
                StringBuilder sql = new StringBuilder();
                sql.Append("select count(id) from P_News");
                return OledbHelper.ExecuteGetCount(sql.ToString());
            }

            /// <summary>
            /// 获得查询分页数据
            /// </summary>
            public DataSet GetPageList(int pageSize, int currentPage, string strWhere, string filedOrder)
            {
                int topNum = pageSize * currentPage;
                StringBuilder strSql = new StringBuilder();

                strSql.Append("select top " + pageSize + " * from P_News");
                if (currentPage > 0)
                {
                    strSql.Append(" where Id not in( select top " + topNum + " Id from P_News");
                    if (strWhere.Trim() != "")
                    {
                        strSql.Append(" where " + strWhere);
                    }
                    strSql.Append(" order by " + filedOrder + ")");
                }
                if (strWhere.Trim() != "")
                {
                    if (currentPage > 0)
                    {
                        strSql.Append(" and " + strWhere);
                    }
                    else
                    {
                        strSql.Append(" where " + strWhere);
                    }
                }
                strSql.Append(" order by " + filedOrder);

                return OledbHelper.Query(strSql.ToString());
            }

            public PModel.PNews GetModel(string id)
            {
                StringBuilder sql = new StringBuilder();
                sql.Append("select * from P_News where Id=" + id);
                DataTable dt = OledbHelper.ExecuteQuery(sql.ToString());
                PModel.PNews model = new PModel.PNews();
                if (dt.Rows.Count > 0)
                {
                    model = ModelHandler<PModel.PNews>.FillModel(dt.Rows[0]);
                }
                return model;
            }

            public void ExSql(string sql)
            {
                OledbHelper.ExecuteSql(sql);
            }

            public bool Update(PModel.PNews model)
            {
                StringBuilder sql = new StringBuilder();
                sql.Append("update P_News set NTitle=@nTitle,NCont=@nCont,NHit=@nHit where Id=@Id");
                OleDbParameter[] parameters = {
					new OleDbParameter("@nTitle", OleDbType.VarChar,100),
					new OleDbParameter("@nCont", OleDbType.VarChar),
                    new OleDbParameter("@nHit", OleDbType.Integer),
                    new OleDbParameter("@Id", OleDbType.Integer)};
                parameters[0].Value = model.NTitle;
                parameters[1].Value = model.NCont;
                parameters[2].Value = model.NHit;
                parameters[3].Value = model.Id;
                return OledbHelper.ExecuteSql(sql.ToString(), parameters) > 0 ? true : false;
            }

            public bool Insert(PModel.PNews model)
            {
                StringBuilder sql = new StringBuilder();
                sql.Append("insert into P_News (NTitle,NCont,NHit,NTime) values (@nTitle,@nCont,@nHit,@nTime)");
                OleDbParameter[] parameters = {
					new OleDbParameter("@nTitle", OleDbType.VarChar,100),
					new OleDbParameter("@nCont", OleDbType.VarChar),
                    new OleDbParameter("@nHit", OleDbType.Integer),
                    new OleDbParameter("@nTime", OleDbType.Date)};
                parameters[0].Value = model.NTitle;
                parameters[1].Value = model.NCont;
                parameters[2].Value = model.NHit;
                parameters[3].Value = model.NTime;
                return OledbHelper.ExecuteSql(sql.ToString(), parameters) > 0 ? true : false;
            }
        }
        #endregion

        #region Used
        public class PUsed
        {
            public PUsed() { }

            public DataSet GetList()
            {
                StringBuilder sql = new StringBuilder();
                sql.Append("select * from P_Used");
                return OledbHelper.Query(sql.ToString());
            }

            /// <summary>
            /// 返回数据总数(分页用到)
            /// </summary>
            public int GetCount(string strWhere)
            {
                StringBuilder sql = new StringBuilder();
                sql.Append("select count(*) as H ");
                sql.Append(" from P_Used ");
                if (strWhere.Trim() != "")
                {
                    sql.Append(" where " + strWhere);
                }
                return Convert.ToInt32(OledbHelper.GetSingle(sql.ToString()));
            }

            /// <summary>
            /// 获得查询分页数据
            /// </summary>
            public DataSet GetPageList(int pageSize, int currentPage, string strWhere, string filedOrder)
            {
                int topNum = pageSize * currentPage;
                StringBuilder strSql = new StringBuilder();

                strSql.Append("select top " + pageSize + " * from P_Used");
                if (currentPage > 0)
                {
                    strSql.Append(" where Id not in( select top " + topNum + " Id from P_Used");
                    if (strWhere.Trim() != "")
                    {
                        strSql.Append(" where " + strWhere);
                    }
                    strSql.Append(" order by " + filedOrder + ")");
                }
                if (strWhere.Trim() != "")
                {
                    if (currentPage > 0)
                    {
                        strSql.Append(" and " + strWhere);
                    }
                    else
                    {
                        strSql.Append(" where " + strWhere);
                    }
                }
                strSql.Append(" order by " + filedOrder);

                return OledbHelper.Query(strSql.ToString());
            }

            public bool Update(PModel.PUsed model)
            {
                StringBuilder sql = new StringBuilder();
                sql.Append("update P_Used set UName=@uName,UInfo=@uInfo,UPic=@uPic,UColor=@uColor where Id=@Id");
                OleDbParameter[] parameters = {
					new OleDbParameter("@uName", OleDbType.VarChar,100),
					new OleDbParameter("@uInfo", OleDbType.VarChar),
                    new OleDbParameter("@uPic", OleDbType.VarChar,100),
                    new OleDbParameter("@uColor", OleDbType.VarChar,50),
                    new OleDbParameter("@Id", OleDbType.Integer)};
                parameters[0].Value = model.UName;
                parameters[1].Value = model.UInfo;
                parameters[2].Value = model.UPic;
                parameters[3].Value = model.UColor;
                parameters[4].Value = model.Id;
                return OledbHelper.ExecuteSql(sql.ToString(), parameters) > 0 ? true : false;
            }

            public bool Insert(PModel.PUsed model)
            {
                StringBuilder sql = new StringBuilder();
                sql.Append("insert into P_Used (UName,UInfo,UPic,UColor) values (@uName,@uInfo,@uPic,@uColor)");
                OleDbParameter[] parameters = {
					new OleDbParameter("@uName", OleDbType.VarChar,100),
					new OleDbParameter("@uInfo", OleDbType.VarChar),
                    new OleDbParameter("@uPic", OleDbType.VarChar,100),
                    new OleDbParameter("@uColor", OleDbType.VarChar,50)};
                parameters[0].Value = model.UName;
                parameters[1].Value = model.UInfo;
                parameters[2].Value = model.UPic;
                parameters[3].Value = model.UColor;
                return OledbHelper.ExecuteSql(sql.ToString(), parameters) > 0 ? true : false;
            }
        }
        #endregion
    }
}
